/****** Object:  StoredProcedure [dbo].[USP_GXBZ_EMR_MJZBL]    Script Date: 2018/12/5 18:45:42 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_GXBZ_EMR_MJZBL]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[USP_GXBZ_EMR_MJZBL]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**********
[版本号]1.0.0.0.0
[创建时间]2018-12-05
[作者]WANG_W
[版权]卫宁健康科技集团股份有限公司
[描述] 
[功能说明]重庆cdc数据上传--门急诊病历GXBZ_EMR_MJZBL数据上传
[返回值]
[调用的USP]
[调用实例] EXEC USP_GXBZ_EMR_MJZBL
[修改历史]
1.0.1	周东东	2018-12-06	---添加字段BLLSH主键约束
							---修改记录的日志表和时间戳表
**********/
CREATE PROC [dbo].[USP_GXBZ_EMR_MJZBL]    
AS 
  SET XACT_ABORT ON 
DECLARE
	--@sKssj VARCHAR(10)='',		--上传开始时间
	--@sJssj VARCHAR(10)=''		--上传结束时间
	@kssj datetime,					--上传开始时间
	@jssj datetime					--上传结束时间
BEGIN                                                                                                  

	
	
	IF NOT EXISTS(SELECT table_time FROM SYS_SJSC_TABLE 
	WHERE TABLE_NAME = 'GXBZ_EMR_MJZBL')
	BEGIN 
		INSERT INTO SYS_SJSC_TABLE(TABLE_NAME,TABLE_TIME)
		VALUES('GXBZ_EMR_MJZBL','2015-07-01')
	END 
	
	--获取已上传的时间
	SELECT @kssj =TABLE_TIME  FROM SYS_SJSC_TABLE(NOLOCK)
	WHERE TABLE_NAME = 'GXBZ_EMR_MJZBL'
	
	--获取源表中最大上传时间
	SELECT @jssj = MAX(TBRQ) FROM MZYZMXB(NOLOCK)
	
	IF(@jssj>ISNULL(@kssj,'1900-01-01'))
	BEGIN
		BEGIN TRY
		BEGIN TRAN

	-------创建临时表  
	CREATE TABLE #TEMP_GXBZ_EMR_MJZBL(  
		BLLSH VARCHAR(64) NULL,
		GRJBXXBSH VARCHAR(64) NULL,
		XGBZ VARCHAR(1) NULL,
		CXJMJKDABH VARCHAR(17) NULL,
		KSBM VARCHAR(64) NULL,
		KSMC VARCHAR(32) NULL,
		YLJGDM VARCHAR(22) NULL,
		YLJGMC VARCHAR(70) NULL,
		JZRQSJ DATETIME NULL,
		JDYSGH VARCHAR(64) NULL,
		JDYSXM VARCHAR(30) NULL,
		WDGLJGDM VARCHAR(22) NULL,
		WDGLJGMC VARCHAR(70) NULL,
		JZLSH VARCHAR(64) NULL,
		JLRQ DATETIME NULL,
		ZRYSGH VARCHAR(64) NULL,
		ZRYSXM VARCHAR(30) NULL,
		GMSBZ VARCHAR(1) NULL,
		GMSMS VARCHAR(100) NULL,
		ZSMS VARCHAR(1024) NULL,
		XBS VARCHAR(1024) NULL,
		JWS VARCHAR(1024) NULL,
		TGJC VARCHAR(500) NULL,
		CZBZDM VARCHAR(1) NULL,
		SZGCJG VARCHAR(100) NULL,
		XYCZDM VARCHAR(64) NULL,
		XYCZMC VARCHAR(512) NULL,
		ZYCZBMDM VARCHAR(64) NULL,
		ZYCZBMMC VARCHAR(512) NULL,
		ZYCZZHDM VARCHAR(64) NULL,
		ZYCZZHMC VARCHAR(512) NULL,
		BZYJ VARCHAR(100) NULL,
		ZZZF VARCHAR(100) NULL,
		YZXMLXDM VARCHAR(2) NULL,
		YZXMLXMC VARCHAR(32) NULL,
		YZJHKSRQ DATETIME NULL,
		YZJHJSRQ DATETIME NULL,
		YZXMMS VARCHAR(1024) NULL,
		YZZXRQ DATETIME NULL,
		YZZXZXM VARCHAR(50) NULL,
		YZZXKSMC VARCHAR(100) NULL,
		YZKLRQ DATETIME NULL,
		YZKLZXM VARCHAR(50) NULL,
		YZKLKSMC VARCHAR(100) NULL,
		YZSHRQ DATETIME NULL,
		YZSHRXM VARCHAR(50) NULL,
		YZQXRQ DATETIME NULL,
		YZQXZXM VARCHAR(50) NULL,
		YZBZXX VARCHAR(1024) NULL,
		YZZXZT VARCHAR(50) NULL,
		MJ VARCHAR(16) NULL,
		TBRQ DATETIME NULL,
		SCZT VARCHAR(1) NULL,
		CS_JYBZ INT NULL,
		SCSJ DATETIME NULL
	) ON [PRIMARY]

	--插业务数据到临时表
	INSERT INTO #TEMP_GXBZ_EMR_MJZBL (BLLSH,GRJBXXBSH, XGBZ, CXJMJKDABH, KSBM, KSMC, YLJGDM, YLJGMC, JZRQSJ, JDYSGH, JDYSXM, 
		WDGLJGDM, WDGLJGMC, JZLSH, JLRQ, ZRYSGH,ZRYSXM, GMSBZ, GMSMS, ZSMS, XBS, JWS, TGJC, CZBZDM, SZGCJG, XYCZDM, 
		XYCZMC, ZYCZBMDM, ZYCZBMMC, ZYCZZHDM, ZYCZZHMC, BZYJ,ZZZF, YZXMLXDM, YZXMLXMC, YZJHKSRQ, YZJHJSRQ, YZXMMS, YZZXRQ,
		YZZXZXM, YZZXKSMC, YZKLRQ, YZKLZXM, YZKLKSMC, YZSHRQ, YZSHRXM,YZQXRQ, YZQXZXM, YZBZXX, YZZXZT, MJ, TBRQ, SCZT, CS_JYBZ, SCSJ) 
	SELECT
		C.YZLSH BLLSH,
		(select top 1 JBXX.GRJBXXBSH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = C.ZJHM) AS GRJBXXBSH,
		A.XGBZ AS XGBZ,
		(select top 1 JBXX.CXJMJKDABH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = C.ZJHM) AS CXJMJKDABH,
		A.KSBM AS KSBM,
		A.KSMC AS KSMC,
		isnull((SELECT SPTDM FROM SYS_ZCJG_DZ where A.YLJGDM=QPTDM),'500103000000001') AS YLJGDM,
		E.JGMC AS YLJGMC,
		A.JZRQSJ AS JZRQSJ,
		A.YSGH AS JDYSGH,
		A.YSQM AS JDYSXM,
		'500103000000001' AS WDGLJGDM,
		'渝中区卫计委' AS WDGLJGMC,
		A.JZLSH AS JZLSH,
		A.JLRQ AS JLRQ,
		C.HDYJSGH AS ZRYSGH,
		C.HDYJSXM AS ZRYSXM,
		A.GMSBZ AS GMSBZ,
		A.GMS AS GMSMS,
		A.ZS AS ZSMS,
		A.XBS AS XBS,
		A.JWS AS JWS,
		A.TGJC AS TGJC,
		A.CZBZDM AS CZBZDM,
		A.SZGCJG AS SZGCJG,
		A.XYZDDM AS XYCZDM,
		A.XYZDMC AS XYCZMC,
		A.ZYBMDM AS ZYCZBMDM,
		A.ZYBMMC AS ZYCZBMMC,
		A.ZYZHDM AS ZYCZZHDM,
		A.ZYZHMC AS ZYCZZHMC,
		A.BZYJ AS BZYJ,
		A.ZZZF AS ZZZF,
		C.YZXMLXDM AS YZXMLXDM,
		C.YZXMLXMC AS YZXMLXMC,
		C.KFSJ AS YZJHKSRQ,
		NULL AS YZJHJSRQ,
		NULL AS YZXMMS,
		C.KFSJ AS YZZXRQ,
		C.FYYJSXM AS YZZXZXM,
		C.FYKSMC AS YZZXKSMC,
		C.KFSJ AS YZKLRQ,
		C.KFYSXM AS YZKLZXM,
		C.JZKSMC AS YZKLKSMC,
		C.KFSJ AS YZSHRQ,
		C.SHYJSXM AS YZSHRXM,
		NULL AS YZQXRQ,
		NULL AS YZQXZXM,
		C.BZ AS YZBZXX,
		NULL AS YZZXZT,
		A.MJ AS MJ,
		A.TBRQ AS TBRQ,
		'0' AS SCZT,
		0 AS CS_JYBZ,
		GETDATE() AS SCSJ
	FROM EMR_MJZBL A(NOLOCK)
	LEFT JOIN MZYZMXB C(NOLOCK) ON A.YLJGDM=C.YLJGDM AND A.JZLSH=C.JZLSH 
	LEFT JOIN SYS_ZCJG_DZ E(NOLOCK) ON A.YLJGDM=E.QPTDM
	WHERE a.TBRQ>ISNULL(@kssj,'1900-01-01')
	and 
	(select top 1 JBXX.GRJBXXBSH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = C.ZJHM) is not NULL
	--删除中间库中重复数据
	DELETE A FROM GXBZ_EMR_MJZBL A,#TEMP_GXBZ_EMR_MJZBL B
	WHERE A.GRJBXXBSH=B.GRJBXXBSH AND A.YLJGDM=B.YLJGDM AND A.BLLSH=B.JZLSH

	--插入数据至中间库
	INSERT INTO GXBZ_EMR_MJZBL (BLLSH,GRJBXXBSH, XGBZ, CXJMJKDABH, KSBM, KSMC, YLJGDM, YLJGMC, JZRQSJ, JDYSGH, JDYSXM, 
		WDGLJGDM, WDGLJGMC, JLRQ, ZRYSGH,ZRYSXM, GMSBZ, GMSMS, ZSMS, XBS, JWS, TGJC, CZBZDM, SZGCJG, XYCZDM, 
		XYCZMC, ZYCZBMDM, ZYCZBMMC, ZYCZZHDM, ZYCZZHMC, BZYJ,ZZZF, YZXMLXDM, YZXMLXMC, YZJHKSRQ, YZJHJSRQ, YZXMMS, YZZXRQ,
		YZZXZXM, YZZXKSMC, YZKLRQ, YZKLZXM, YZKLKSMC, YZSHRQ, YZSHRXM,YZQXRQ, YZQXZXM, YZBZXX, YZZXZT, MJ, TBRQ, SCZT, CS_JYBZ, SCSJ)    
	SELECT BLLSH,GRJBXXBSH, XGBZ, CXJMJKDABH, KSBM, KSMC, YLJGDM, YLJGMC, JZRQSJ, JDYSGH, JDYSXM, 
		WDGLJGDM,  WDGLJGMC, JLRQ, ZRYSGH,ZRYSXM, GMSBZ, GMSMS, ZSMS, XBS, JWS, TGJC, CZBZDM, SZGCJG, XYCZDM, 
		XYCZMC, ZYCZBMDM, ZYCZBMMC, ZYCZZHDM, ZYCZZHMC, BZYJ,ZZZF, YZXMLXDM, YZXMLXMC, YZJHKSRQ, YZJHJSRQ, YZXMMS, YZZXRQ,
		YZZXZXM, YZZXKSMC, YZKLRQ, YZKLZXM, YZKLKSMC, YZSHRQ, YZSHRXM,YZQXRQ, YZQXZXM, YZBZXX, YZZXZT, MJ, TBRQ, SCZT, CS_JYBZ, SCSJ
	FROM #TEMP_GXBZ_EMR_MJZBL

	COMMIT TRAN

	   SELECT 'T' AS SRESULT, '表：GXBZ_EMR_MJZBL数据抽取成功！' AS SHINT
   
	   INSERT INTO SYS_SCSJ_LOG(CONTENT,TABLENAME,RECORDTIME,RST,PROCNAME) 
	   VALUES('表：GXBZ_EMR_MJZBL数据抽取成功','GXBZ_EMR_MJZBL',GETDATE(),'T','USP_WD_GXBZ_EMR_MJZBL')  
   
	 -- INSERT INTO SYS_SJSC_TABLENAME(TABLE_NAME, SCKSSJ, SCJSSJ, BZ)
	   --VALUES('GXBZ_EMR_MJZBL',@sKssj,@sJssj,'')
		--更新上传时间
		UPDATE SYS_SJSC_TABLE SET TABLE_TIME = @jssj WHERE TABLE_NAME = 'GXBZ_EMR_MJZBL'
		
		

	END TRY BEGIN CATCH
		SELECT 'F' AS SRESULT, '表：GXBZ_EMR_MJZBL数据抽取失败！' AS SHINT, ERROR_MESSAGE() AS MSG
		ROLLBACK TRAN

		/*记录每步执行的记录日志*/
		INSERT INTO SYS_SCSJ_LOG(CONTENT,TABLENAME,RECORDTIME,RST,PROCNAME,MSG,ERRORCODE) 
		VALUES('表：GXBZ_EMR_MJZBL数据抽取失败','GXBZ_EMR_MJZBL',GETDATE(),'F','USP_GXBZ_EMR_MJZBL',ERROR_MESSAGE(),ERROR_NUMBER())
	END CATCH
	END
END

